"""
cron: */10 * * * *
new Env('商品价格监控专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup
import re

class PriceMonitorPro:
    def __init__(self):
        # 初始化数据库连接
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 用户表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_monitor_users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL,
                password VARCHAR(100) NOT NULL,
                email VARCHAR(100),
                phone VARCHAR(20),
                vip_level INT DEFAULT 0,
                vip_expire DATETIME,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_username (username),
                INDEX idx_vip_level (vip_level)
            )
        """)
        
        # 商品表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_monitor_products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                product_id VARCHAR(100) NOT NULL,
                title VARCHAR(500),
                platform VARCHAR(50),
                category VARCHAR(100),
                user_id INT,
                target_price DECIMAL(10,2),
                lowest_price DECIMAL(10,2),
                highest_price DECIMAL(10,2),
                avg_price DECIMAL(10,2),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_product_id (product_id),
                INDEX idx_user_id (user_id)
            )
        """)
        
        # 价格历史表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_price_history (
                id INT AUTO_INCREMENT PRIMARY KEY,
                product_id VARCHAR(100),
                price DECIMAL(10,2),
                original_price DECIMAL(10,2),
                discount_rate DECIMAL(5,2),
                platform VARCHAR(50),
                coupon_info TEXT,
                promotion_info TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_product_id (product_id),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 用户通知表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_price_alerts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                product_id VARCHAR(100),
                alert_price DECIMAL(10,2),
                current_price DECIMAL(10,2),
                status VARCHAR(20),
                notify_type VARCHAR(20),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_user_id (user_id),
                INDEX idx_status (status)
            )
        """)
        
        # 返利记录表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_rebate_records (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT,
                product_id VARCHAR(100),
                order_amount DECIMAL(10,2),
                rebate_amount DECIMAL(10,2),
                status VARCHAR(20),
                platform VARCHAR(50),
                order_id VARCHAR(100),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_user_id (user_id),
                INDEX idx_order_id (order_id)
            )
        """)

    def monitor_jd_price(self, product_id):
        """监控京东商品价格"""
        try:
            url = f'https://item.jd.com/{product_id}.html'
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
                'Referer': 'https://www.jd.com/'
            }
            
            # 获取商品详情
            response = requests.get(url, headers=headers)
            soup = BeautifulSoup(response.text, 'html.parser')
            
            # 获取商品标题
            title = soup.find('div', class_='sku-name').text.strip()
            
            # 获取价格API
            price_url = f'https://p.3.cn/prices/mgets?skuIds=J_{product_id}'
            price_response = requests.get(price_url, headers=headers)
            price_data = price_response.json()[0]
            
            current_price = float(price_data['p'])
            original_price = float(price_data['m'])
            discount_rate = round((1 - current_price/original_price) * 100, 2) if original_price > 0 else 0
            
            return {
                'title': title,
                'current_price': current_price,
                'original_price': original_price,
                'discount_rate': discount_rate
            }
            
        except Exception as e:
            print(f"获取商品 {product_id} 的价格失败: {str(e)}")
            return None
            
    def monitor_taobao_price(self, product_id):
        """监控淘宝商品价格"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_7_1 like Mac OS X) AppleWebKit/605.1.15',
                'Cookie': os.environ.get('TAOBAO_COOKIE', '')  # 需要登录cookie
            }
            
            url = f'https://detail.tmall.com/item.htm?id={product_id}'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                
                # 获取商品标题
                title = soup.find('title').text.strip().replace(' - 淘宝网', '')
                
                # 获取价格（这里需要处理动态加载的价格）
                price_api = f'https://mdskip.taobao.com/core/initItemDetail.htm?itemId={product_id}'
                price_response = requests.get(price_api, headers=headers)
                price_data = price_response.json()
                
                current_price = float(price_data['data']['price'])
                original_price = float(price_data['data']['originalPrice'])
                promotion_info = json.dumps(price_data['data']['promotion'])
                
                return {
                    'title': title,
                    'current_price': current_price,
                    'original_price': original_price,
                    'promotion_info': promotion_info
                }
                
        except Exception as e:
            print(f"获取淘宝商品 {product_id} 的价格失败: {str(e)}")
            return None

    def monitor_pdd_price(self, product_id):
        """监控拼多多商品价格"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_7_1 like Mac OS X) AppleWebKit/605.1.15',
                'Cookie': os.environ.get('PDD_COOKIE', '')  # 需要登录cookie
            }
            
            url = f'https://mobile.yangkeduo.com/goods.html?goods_id={product_id}'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                # 解析商品数据
                data = re.search(r'window\.rawData=(.*?)</script>', response.text)
                if data:
                    product_data = json.loads(data.group(1))
                    
                    return {
                        'title': product_data['goods']['goodsName'],
                        'current_price': float(product_data['goods']['minGroupPrice']) / 100,
                        'original_price': float(product_data['goods']['marketPrice']) / 100,
                        'promotion_info': json.dumps(product_data['goods']['promotionV2'])
                    }
                    
        except Exception as e:
            print(f"获取拼多多商品 {product_id} 的价格失败: {str(e)}")
            return None

    def update_product_statistics(self, product_id):
        """更新商品价格统计数据"""
        try:
            query = """
                UPDATE ningm_monitor_products p
                SET lowest_price = (
                    SELECT MIN(price) FROM ningm_price_history 
                    WHERE product_id = %s
                ),
                highest_price = (
                    SELECT MAX(price) FROM ningm_price_history 
                    WHERE product_id = %s
                ),
                avg_price = (
                    SELECT AVG(price) FROM ningm_price_history 
                    WHERE product_id = %s
                )
                WHERE p.product_id = %s
            """
            self.cursor.execute(query, (product_id, product_id, product_id, product_id))
            self.connection.commit()
            
        except Exception as e:
            print(f"更新商品统计数据失败: {str(e)}")

    def generate_purchase_link(self, product_id, platform, user_id):
        """生成带返利的购买链接"""
        try:
            if platform == 'taobao':
                base_url = f'https://item.taobao.com/item.htm?id={product_id}'
                return f'{base_url}&ref=ningm_{user_id}'
            elif platform == 'jd':
                base_url = f'https://item.jd.com/{product_id}.html'
                return f'{base_url}?ref=ningm_{user_id}'
            elif platform == 'pdd':
                base_url = f'https://mobile.yangkeduo.com/goods.html?goods_id={product_id}'
                return f'{base_url}&ref=ningm_{user_id}'
            
        except Exception as e:
            print(f"生成购买链接失败: {str(e)}")
            return None

    def send_notification(self, user_id, message, notify_type='email'):
        """发送通知"""
        try:
            # 获取用户联系方式
            query = "SELECT email, phone, vip_level FROM ningm_monitor_users WHERE id = %s"
            self.cursor.execute(query, (user_id,))
            user = self.cursor.fetchone()
            
            if not user:
                return False
                
            email, phone, vip_level = user
            
            if notify_type == 'email' and email:
                # TODO: 实现邮件发送
                pass
            elif notify_type == 'sms' and phone and vip_level > 0:
                # TODO: 实现短信发送（仅VIP用户）
                pass
                
            return True
            
        except Exception as e:
            print(f"发送通知失败: {str(e)}")
            return False

    def check_price_alerts(self, product_id, current_price, purchase_link):
        """检查价格提醒"""
        query = """
            SELECT p.product_id, p.title, p.platform, p.target_price, 
                   h.price as current_price
            FROM ningm_monitor_products p
            JOIN ningm_price_history h ON p.product_id = h.product_id
            WHERE h.create_time = (
                SELECT MAX(create_time) 
                FROM ningm_price_history 
                WHERE product_id = p.product_id
            )
            AND h.price <= p.target_price
            AND NOT EXISTS (
                SELECT 1 FROM ningm_price_alerts a 
                WHERE a.product_id = p.product_id 
                AND a.status = 'PENDING'
            )
        """
        self.cursor.execute(query)
        alerts = self.cursor.fetchall()
        
        for alert in alerts:
            product_id, title, platform, target_price, current_price = alert
            
            # 创建提醒
            insert_query = """
                INSERT INTO ningm_price_alerts 
                (user_id, product_id, alert_price, current_price, status, notify_type) 
                VALUES (%s, %s, %s, %s, %s, %s)
            """
            self.cursor.execute(insert_query, (1, product_id, target_price, current_price, 'PENDING', 'email'))
            
            # TODO: 发送通知（邮件、微信等）
            print(f"价格提醒: {title} 当前价格 {current_price} 已低于目标价格 {target_price}")
            
        self.connection.commit()

    def run(self):
        """运行监控"""
        try:
            # 获取所有需要监控的商品
            self.cursor.execute("""
                SELECT p.product_id, p.platform, p.user_id, u.vip_level 
                FROM ningm_monitor_products p
                JOIN ningm_monitor_users u ON p.user_id = u.id
            """)
            products = self.cursor.fetchall()
            
            for product_id, platform, user_id, vip_level in products:
                # 根据用户VIP等级决定更新频率
                if vip_level == 0 and time.time() % 3600 > 0:  # 免费用户1小时更新一次
                    continue
                elif vip_level == 1 and time.time() % 1800 > 0:  # VIP1用户30分钟更新一次
                    continue
                # VIP2及以上用户实时更新
                
                data = None
                if platform == 'jd':
                    data = self.monitor_jd_price(product_id)
                elif platform == 'taobao':
                    data = self.monitor_taobao_price(product_id)
                elif platform == 'pdd':
                    data = self.monitor_pdd_price(product_id)
                
                if data:
                    # 保存价格历史
                    insert_query = """
                        INSERT INTO ningm_price_history 
                        (product_id, price, original_price, discount_rate, platform, promotion_info) 
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """
                    discount_rate = round((1 - data['current_price']/data['original_price']) * 100, 2) if data['original_price'] > 0 else 0
                    
                    self.cursor.execute(insert_query, (
                        product_id,
                        data['current_price'],
                        data['original_price'],
                        discount_rate,
                        platform,
                        data.get('promotion_info', '')
                    ))
                    
                    # 更新商品统计数据
                    self.update_product_statistics(product_id)
                    
                    # 生成购买链接
                    purchase_link = self.generate_purchase_link(product_id, platform, user_id)
                    
                    # 检查是否需要发送通知
                    self.check_price_alerts(product_id, data['current_price'], purchase_link)
                
                # 根据VIP等级设置请求延迟
                if vip_level == 0:
                    time.sleep(5)
                elif vip_level == 1:
                    time.sleep(2)
                else:
                    time.sleep(1)
                
            self.connection.commit()
            
        except Exception as e:
            print(f"监控执行失败: {str(e)}")
            self.connection.rollback()
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    monitor = PriceMonitorPro()
    monitor.run() 